
from mini_tools.mytools import *
from mini_tools.myexcel import *
from config import *
from sql import sqlseverDB
import pandas as pd
import concurrent.futures as thddd
import os

ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'sumaitongpol', SQL_USER,SQL_PWD)

def get_excel():
    sqlstr='''select top 1000 itemid,yntitle,yntitlercode from proshopeeph order by itemid
    '''
    fqz='标题随机码'
    cur_map=DBO_MAP.get('ProShopeePh')
    sqlToExcel(fqz,ms,sqlstr,dbpmap=cur_map)

def read_excel(file_path):
    # 读取 Excel 文件
    df = pd.read_excel(file_path,dtype={'itemid': str,'shopid':str,'skuid':str})
    df.drop(columns=['sku2Img','catidJson'], inplace=True)
    df.rename(columns={'sku1Img': 'skuImg'}, inplace=True)
    # 将 NaN 值替换为 None
    
    df = df.applymap(lambda x: None if pd.isna(x) else x)

    # 将数据框转换为字典
    data_dict = df.to_dict(orient='records')

    return data_dict

currency='php'
pingtai='菲律宾虾皮'
ycpmbid_itemids=[]
def upload_xp_common(pdata:dict):
    datatype=1
    itemid=pdata['itemid']

    rs=ms.ExecQuery('select count(*) from proshopeeph where itemid=?',(itemid,))
    if rs[0][0]==0:
        zds=list(pdata.keys())
        params=list(pdata.values())
        zds=zds+['currency','datatype','PingTai']
        params=params+[currency,datatype,pingtai]
        zdstr=','.join(zds)
        wstr=','.join(['?' for _ in params])
        sqlstr1=f'''insert into proshopeeph({zdstr}) values({wstr})'''
        aff1=ms.ExecNoQuery(sqlstr1,params)
        print(f"itemid:{itemid},skuid:{pdata['skuid']},保存到《proshopeeph》状态:{aff1}")
        # if aff1>0 and itemid not in ycpmbid_itemids:
        #     zds2=['itemid','pmbid','rating_star','skuSold','historical_sold','cmt_count','price']
        #     params2=[pdata[zd] for zd in zds2]
        #     zds2=zds2+['currency','datatype','PingTai']
        #     params2=params2+[currency,datatype,pingtai]
        #     zdstr2=','.join(zds2)
        #     wstr2=','.join(['?' for _ in params2])
        #     sqlstr2=f'insert into proshopeeph_pmbid({zdstr2}) values({wstr2})'
        #     aff2=ms.ExecNoQuery(sqlstr2,params2)
        #     print(f'itemid:{itemid},保存到《proshopeeph_pmbid》状态:{aff2}')
        #     ycpmbid_itemids.append(itemid)

    else:
        print(f'数据库已存在:{itemid}')

def dr_xp_common():
    dir_path='shpee25-2-24'
    files=os.listdir(dir_path)
    print(f'共{len(files)}个文件待上传')
    for file_name in files:
        print(f'开始上传：《{file_name}》')
        cur_path=os.path.join(dir_path,file_name)
        datas=read_excel(cur_path)
        task_count=len(datas)
        print(f'任务总数剩余:{task_count}')
        with thddd.ThreadPoolExecutor(max_workers=20) as t:
            tasks=[t.submit(upload_xp_common,data) for data in datas]
            for t0 in thddd.as_completed(tasks):
                try:
                    res=t0.result()
                except Exception as e:
                    print(f'线程错误:{e}')
                task_count-=1
                print(f'任务队列剩余:{task_count}')
        print(f'《{file_name}》,上传完毕')

dr_xp_common()
    